package com.jvxb.manage.livable.util;

import lombok.extern.log4j.Log4j2;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author jvxb
 * @since 2022-02-17
 */
@Log4j2
@Component
public class JdbcUtil {

    public static final String CONNECT_DEFAULT_CONF = "?useUnicode=true&serverTimezone=GMT%2B8&characterEncoding=utf8&useSSL=false";

    public static String getConnectUrl(String host, String port) {
        return getConnectUrl(host, port, null);
    }

    public static String getConnectUrl(String host, String port, String dbName) {
        return "jdbc:mysql://" + host + ":" + port + (dbName == null || dbName.length() == 0 ? "" : ("/" + dbName)) + CONNECT_DEFAULT_CONF;
    }

    public static Connection getConnection(String host, String port) throws Exception {
        return getConnection(host, port, null);
    }

    public static Connection getConnection(String host, String port, String dbName) throws Exception {
        String username = "root";
        String password = "123456";
        Class.forName("com.mysql.jdbc.Driver");
        String url = getConnectUrl(host, port, dbName);
        Connection con = DriverManager.getConnection(url, username, password);
        return con;
    }

    public static void execute(Connection conn, String sql) throws SQLException {
        execute(conn, sql, Collections.emptyList());
    }

    public static void execute(Connection conn, String sql, List<Object> parameters) throws SQLException {
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(sql);
            setParameters(stmt, parameters);
            stmt.executeUpdate();
        } finally {
            close((Statement) stmt);
        }
    }

    private static void setParameters(PreparedStatement stmt, List<Object> parameters) throws SQLException {
        int i = 0;
        for (int size = parameters.size(); i < size; ++i) {
            Object param = parameters.get(i);
            stmt.setObject(i + 1, param);
        }
    }

    public static void executeSql(String host, String port, String sql) throws Exception {
        executeSql(host, port, sql, null);
    }

    public static void executeSql(String host, String port, String sql, String dbName) throws Exception {
        String username = "root";
        String password = "123456";
        Class.forName("com.mysql.jdbc.Driver");
        String url = getConnectUrl(host, port, dbName);
        try (Connection con = DriverManager.getConnection(url, username, password);
             PreparedStatement preparedStatement = con.prepareStatement(sql);
        ) {
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            log.error(e, e);
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    /**
     * 取查询结果的第一行，并将结果转为 Map.
     */
    public static Map<String, Object> queryOne(String host, String port, String sql) throws Exception {
        return queryOne(host, port, sql, null);
    }

    /**
     * 取查询结果的第一行，并将结果转为 Map.
     */
    public static Map<String, Object> queryOne(String host, String port, String sql, String dbName) throws Exception {
        String username = "root";
        String password = "123456";
        Class.forName("com.mysql.jdbc.Driver");
        String url = getConnectUrl(host, port, dbName);
        QueryRunner runner = new QueryRunner();
        try (Connection con = DriverManager.getConnection(url, username, password)) {
            return runner.query(con, sql, new MapHandler());
        }
    }

    /**
     * 取查询结果的所有行，并将每一行的结果转为 Map.
     */
    public static List<Map<String, Object>> queryList(String host, String port, String sql) throws Exception {
        return queryList(host, port, sql, null);
    }

    /**
     * 取查询结果的所有行，并将每一行的结果转为 Map.
     */
    public static List<Map<String, Object>> queryList(String host, String port, String sql, String dbName) throws Exception {
        String username = "root";
        String password = "123456";
        Class.forName("com.mysql.jdbc.Driver");
        String url = getConnectUrl(host, port, dbName);
        QueryRunner runner = new QueryRunner();
        try (Connection con = DriverManager.getConnection(url, username, password)) {
            return runner.query(con, sql, new MapListHandler());
        }
    }

    public static List<Map<String, Object>> queryList(Connection conn, String sql) throws SQLException {
        List<Map<String, Object>> rows = new ArrayList();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            ResultSetMetaData rsMeta = rs.getMetaData();

            while(rs.next()) {
                Map<String, Object> row = new LinkedHashMap();
                int i = 0;

                for(int size = rsMeta.getColumnCount(); i < size; ++i) {
                    String columName = rsMeta.getColumnLabel(i + 1);
                    Object value = rs.getObject(i + 1);
                    row.put(columName, value);
                }

                rows.add(row);
            }
        } finally {
            close(rs);
            close((Statement)stmt);
        }
        return rows;
    }

    /**
     * 取查询结果的第一行，第一列。并将值转为 String。
     */
    public static String queryString(String host, String port, String sql) throws Exception {
        return queryString(host, port, sql, null);
    }

    /**
     * 取查询结果的第一行，第一列。并将值转为 String。
     */
    public static String queryString(String host, String port, String sql, String dbName) throws Exception {
        String username = "root";
        String password = "123456";
        Class.forName("com.mysql.jdbc.Driver");
        String url = getConnectUrl(host, port, dbName);
        QueryRunner runner = new QueryRunner();
        try (Connection con = DriverManager.getConnection(url, username, password)) {
            Object query = runner.query(con, sql, new ScalarHandler<>());
            return query == null ? null : query.toString();
        }
    }

    /**
     * 取查询结果的所有行，第一列。并将值转为 String。
     */
    public static List<String> queryListString(String host, String port, String sql) throws Exception {
        return queryListString(host, port, sql, null);
    }

    /**
     * 取查询结果的所有行，第一列。并将值转为 String。
     */
    public static List<String> queryListString(String host, String port, String sql, String dbName) throws Exception {
        String username = "root";
        String password = "123456";
        Class.forName("com.mysql.jdbc.Driver");
        String url = getConnectUrl(host, port, dbName);
        QueryRunner runner = new QueryRunner();
        try (Connection con = DriverManager.getConnection(url, username, password)) {
            List<Object> query = runner.query(con, sql, new ColumnListHandler<>());
            return query == null ? null : query.stream().map(e -> e.toString()).collect(Collectors.toList());
        }
    }

    public static void close(Statement x) {
        if (x != null) {
            try {
                x.close();
            } catch (Exception var3) {
                boolean printError = true;
                if (var3 instanceof SQLRecoverableException && "Closed Connection".equals(var3.getMessage())) {
                    printError = false;
                }

                if (printError) {
                    log.error("close statement error", var3);
                }
            }
        }
    }

    public static void close(Connection x) {
        if (x != null) {
            try {
                if (x.isClosed()) {
                    return;
                }

                x.close();
            } catch (Exception var2) {
                log.debug("close connection error", var2);
            }

        }
    }

    public static void close(ResultSet x) {
        if (x != null) {
            try {
                x.close();
            } catch (Exception var2) {
                log.debug("close result set error", var2);
            }

        }
    }

}
